/**
 * 
 */
package com.james.wee.shuangseqiu.db;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.james.wee.shuangseqiu.bo.Cardno;
import com.james.wee.shuangseqiu.bo.IDBBo;

/**
 * @author Administrator
 * 
 */
public class DB {
	private static Connection conn = null;
	private static String user = "root";
	private static String password = "root";
	private static String url = "jdbc:mysql://localhost:3306/shuangseqiu";
	private static final DB db = new DB();
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	private DB() {
		try {
			conn = getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static DB getInstance() {
		try {
			if (null == conn || conn.isClosed())
				conn = getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return db;
	}

	public  Connection getConn() {
		return conn;
	}

	public  void setConn(Connection conn) {
		DB.conn = conn;
	}

	private static Connection getConnection() throws Exception {
		return DriverManager.getConnection(url, user, password);
	}

	public IDBBo save(IDBBo obj) throws Exception {
		java.sql.Statement stmt = null;
		stmt = conn.createStatement();
		stmt.executeUpdate(obj.insertSql());
		stmt.close();
		stmt = conn.createStatement();
		java.sql.ResultSet rs = stmt.executeQuery(obj.getIdSql());
		if (null != rs && rs.next()) {
			obj.setId(rs.getInt(1));
			rs.close();
		}
		stmt.close();
		return obj;
	}

	public IDBBo update(IDBBo obj) throws Exception {
		java.sql.Statement stmt = null;
		stmt = conn.createStatement();
		stmt.executeUpdate(obj.updateSql());
		stmt.close();
		return obj;
	}

	public IDBBo updateByMethod(IDBBo obj,Class<?> clazz, String methodName) throws Exception {
		java.sql.Statement stmt = null;
		stmt = conn.createStatement();
		Method mehtod = clazz.getMethod(methodName, null);
		if (null != mehtod)
			stmt.executeUpdate(mehtod.invoke(obj, null).toString());
		stmt.close();
		return obj;
	}

	public List<IDBBo> queryBoList(String sql, Class<?> clazz) throws Exception {
		java.sql.Statement stmt = null;
		stmt = conn.createStatement();
		java.sql.ResultSet rs = stmt.executeQuery(sql);
		ResultSetMetaData rsmd = rs.getMetaData();
		int columncount = rsmd.getColumnCount();
		IDBBo op = null;
		Method[] mets = clazz.getDeclaredMethods();
		Map<String, Method> map = new HashMap<String, Method>();
		List<IDBBo> reslist = new ArrayList<IDBBo>();
		for (Method m : mets) {
			map.put(m.getName().toLowerCase(), m);
		}
		String tmp = null;
		Method method = null;
		Object objvalue = null;
		while (rs.next()) {
			op = (IDBBo) clazz.newInstance();
			for (int i = 1; i <= columncount; i++) {
				tmp = "set" + rsmd.getColumnName(i).toLowerCase();
				method = map.get(tmp);
				objvalue = rs.getObject(i);
				if (null != method && null!=objvalue) {
					method.invoke(op, objvalue);
				}
			}
			reslist.add(op);
		}
		rs.close();
		stmt.close();
		return reslist;
	}

	public static void main(String[] args) {

		try {
			List<IDBBo> reslist = DB.getInstance().queryBoList(
					"select * from cardno" + " where id<10", Cardno.class);
			for (IDBBo i : reslist)
				System.out.println(((Cardno) i).getN_1());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
